﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ExtBusiness.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using WF_DataAccess;
using WF_Business;
using ChBusiness;

namespace ExtBusiness.User
{
    public class UserManager
    {
        /// <summary>
        /// 检测用户是否存在
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>用户存在则返回true,否则返回false</returns>  
        public static bool CheckUserExist(string userName)
        {
            string strSql = string.Empty;
            strSql = string.Format("select * from sys_user a where a.userid = '{0}'", userName);

            DataTable dt = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dt);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="userID">登陆帐号</param>
        /// <param name="userName">用户名称</param>
        /// <param name="userPwd">密码</param>
        /// <param name="type">用户类型</param>
        /// <param name="tel">电话</param>
        /// <param name="mobile">手机</param>
        /// <param name="address">地址</param>
        /// <param name="strIdType">证件类别</param>
        /// <param name="strIdNumber">证件号码</param>
        /// <param name="strFax">传真</param>
        /// <param name="strEmail">email.</param>
        /// <param name="strPostCode">邮政编码</param>
        public static void CreateUser(string userID, string userName, string userPwd, int type, string tel,
             string mobile, string address, string strIdType, string strIdNumber, string strFax,
            string strEmail, string strPostCode)
        {
            userName = userName.ToUpperInvariant();
            string strPass = Admin.User.Md5(userPwd);

            //检查用户是否存在
            if (CheckUserExist(userID))
            {
                throw new DBConcurrencyException("用户名已存在！");
            }

            try
            {
                //创建用户
                string strSql = string.Format(@"insert into sys_user(USERID,USERNAME,USERPWD,QUERY_PASS,TYPE,TEL,MOBILE,
                 ADDRESS,IDTYPE,IDNUMBER,FAX,EMAIL,POSTCODE,ISVALID,CAN_SYNC)
                 values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',1,1)",
                     userID, userName, strPass, userPwd, type, tel, mobile, address, strIdType, strIdNumber, strFax, strEmail, strPostCode);
                SysParams.OAConnection().RunSql(strSql);
            }
            catch
            {
                throw new DBConcurrencyException("创建新用户失败！");
            }
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="userID">登陆帐号</param>
        /// <param name="userName">用户名称</param>
        /// <param name="type">用户类型</param>
        /// <param name="tel">电话</param>
        /// <param name="mobile">手机</param>
        /// <param name="address">地址</param>
        /// <param name="strIdType">证件类别</param>
        /// <param name="strIdNumber">证件号码</param>
        /// <param name="strFax">传真</param>
        /// <param name="strEmail">email.</param>
        /// <param name="strPostCode">邮政编码</param>
        public static void UpdateUser(string id, string userID, string userName, int type, string tel,
             string mobile, string address, string strIdType, string strIdNumber, string strFax,
            string strEmail, string strPostCode)
        {
            try
            {
                string strSql = string.Format(@"update sys_user set userID = '{1}', username = '{2}',
                     type = '{3}',tel = '{4}',mobile = '{5}',address = '{6}',IDTYPE = '{7}',
                     IDNUMBER = '{8}',FAX = '{9}',EMAIL = '{10}',POSTCODE = '{11}',CAN_SYNC=1 where id = '{0}'",
                     id, userID, userName, type, tel, mobile, address, strIdType, strIdNumber, strFax,
                     strEmail, strPostCode);

                SysParams.OAConnection().RunSql(strSql);
                //如果是地信中心用户信息则更改标志位
                strSql = string.Format(@"update sys_user t set NEED_POST =1 where t.SYNC_TYPE=2 
                    and userid='{0}'",userID);
                SysParams.OAConnection().RunSql(strSql);
            }
            catch
            {
                throw new DBConcurrencyException("修改新用户失败！");
            }
        }

        /// <summary>
        /// 根据用户id重置用户密码与用户ID相同
        /// </summary> 
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public static void UpdatePassword(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                string strSql = string.Format("update sys_user set query_pass =userid,USERPWD=fn_md5(userid) where id = '{0}'", id);

                SysParams.OAConnection().RunSql(strSql);

                //如果是地信中心用户信息则更改标志位
                strSql = string.Format(@"update sys_user t set NEED_POST =1 where t.SYNC_TYPE=2 
                    and id='{0}'", id);

                SysParams.OAConnection().RunSql(strSql);
            }
        }
        /// <summary>
        /// 根据用户ID重置用户密码为随机的6位密码
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <!--addby zhongjian 20090927-->
        public static void UpdatePassSet(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                string strSql = string.Format("update sys_user set query_pass =fun_pass(6),USERPWD=fn_md5(fun_pass(6)) where id = '{0}'", id);

                SysParams.OAConnection().RunSql(strSql);

                //如果是地信中心用户信息则更改标志位
                strSql = string.Format(@"update sys_user t set NEED_POST =1 where t.SYNC_TYPE=2 
                    and id='{0}'", id);
                SysParams.OAConnection().RunSql(strSql);
            }
        }
        /// <summary>
        /// 根据用户ID重置用户密码为指定密码
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="strUserPwd">指定的密码</param>
        /// <!--addby zhongjian 20090927-->
        public static void UpdatePassSet(string id,string strUserPwd)
        {
            if (!string.IsNullOrEmpty(id))
            {
                string strSql = string.Format("update sys_user set query_pass ='{1}',USERPWD=fn_md5('{1}') where id = '{0}'", id, strUserPwd);

                SysParams.OAConnection().RunSql(strSql);

                //如果是地信中心用户信息则更改标志位
                strSql = string.Format(@"update sys_user t set NEED_POST =1 where t.SYNC_TYPE=2 
                    and id='{0}'", id);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        /// <!--添加按注册时间排序 update by zhongjian-->
        public DataTable GetUser()
        {
            DataTable dtTemp = new DataTable();
            string strSql = "select * from sys_user order by createdate desc";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="userID">登陆帐号</param>
        /// <param name="userName">用户名称</param>
        /// <param name="userPwd">密码</param>
        /// <param name="type">用户类型</param>
        /// <param name="tel">电话</param>
        /// <param name="mobile">手机</param>
        /// <param name="address">地址</param>
        /// <param name="strIdType">证件类别</param>
        /// <param name="strIdNumber">证件号码</param>
        /// <param name="strFax">传真</param>
        /// <param name="strEmail">email.</param>
        /// <param name="strPostCode">邮政编码</param>
        /// <param name="strUnitsID">单位ID</param>
        public static void CreateUser(string userID, string userName, string userPwd, int type, string tel,string mobile, string address, string strIdType, string strIdNumber, string strFax,
            string strEmail, string strPostCode, string strUnitsID)
        {
            userName = userName.ToUpperInvariant();
            string strPass = Admin.User.Md5(userPwd);
            string strUserType = "0";//用户类型(0:个人用户;1:单位用户)
            string strSql = string.Empty;
            //检查用户是否存在
            if (CheckUserExist(userID))
            {
                throw new DBConcurrencyException("用户名已存在！");
            }

            try
            {
                if (!string.IsNullOrEmpty(strUnitsID))
                    strUserType = "1";//单位用户
                //创建用户
                strSql = string.Format(@"insert into sys_user(USERID,USERNAME,USERPWD,QUERY_PASS,TYPE,TEL,MOBILE,
                 ADDRESS,IDTYPE,IDNUMBER,FAX,EMAIL,POSTCODE,ISVALID,CAN_SYNC,unitsid,usertype)
                 values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',1,1,'{13}','{14}')",
                     userID, userName, strPass, userPwd, type, tel, mobile, address, strIdType, strIdNumber, strFax, strEmail,
                     strPostCode, strUnitsID, strUserType);

                SysParams.OAConnection().RunSql(strSql);
            }
            catch
            {
                throw new DBConcurrencyException("创建新用户失败！");
            }
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strUserName">用户名称</param>
        /// <param name="UnitsName">单位名称</param>
        /// <param name="UnitsID">单位ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100326-->
        public DataTable GetUnitUser(string strUserID, string strUserName, string UnitsName, string UnitsID,string strAdmin)
        {
            DataTable dtTemp=new DataTable();
            string strSql = string.Format(@"select a.*,
                                                   case
                                                     when a.usertype = '1' then
                                                      (select unitsname
                                                         from sys_units b
                                                        where b.id = a.unitsid
                                                          and rownum < 2)
                                                     else
                                                      ''
                                                   end unitsname
                                              from sys_user a where 1=1 ");
            if (!string.IsNullOrEmpty(strUserID))
                strSql += string.Format(" and userid='{0}'", strUserID);
            if (!string.IsNullOrEmpty(strUserName))
                strSql += string.Format(" and username like '%{0}%'", strUserName);
            if (!string.IsNullOrEmpty(UnitsName))
                strSql += string.Format(" and a.unitsid in (select id from sys_units where unitsname like '%{0}%')", UnitsName);
            if (!string.IsNullOrEmpty(UnitsID))
                strSql += string.Format(" and a.unitsid ='{0}'", UnitsID);
            if (!string.IsNullOrEmpty(strAdmin))
            {
                strSql += string.Format(" and a.UNITSADMIN ='{0}'", strAdmin);
            }
            strSql += string.Format(" order by unitsname");
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 删除单位信息
        /// </summary>
        /// <param name="strID">单位表ID</param>
        /// <!--addby zhongjian 20100412-->
        public static void DeleteUnitsInfo(string strID)
        {
            string strSql = string.Format("Delete from sys_units where id ='{0}'", strID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        /// <!--添加按注册时间排序 update by zhongjian-->
        public DataTable GetUser(string strId)
        {
            DataTable dtTemp = new DataTable();
            string strSql = "select * from sys_user where id='" + strId + "' order by createdate desc";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 删除用户信息单
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteUser(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                IDataAccess idaTemp= SysParams.OAConnection(true);
                try
                {
                    //将用户信息记录到历史表
                    string strSql = string.Format("insert into sys_user_old select * from sys_user where id='{0}'", id);
                    idaTemp.RunSql(strSql);

                    //删除
                    strSql = string.Format("Delete from sys_user where id ='{0}'", id);
                    idaTemp.RunSql(strSql);

                    idaTemp.Close(true);
                }
                catch
                {
                    idaTemp.Close(false);
                }
            }
        }


        /// <summary>
        /// 设置用户类型
        /// </summary>
        /// <param name="strID">用户唯一标识id</param>
        /// <param name="strUserType">用户类型</param>
        public static void SaveUserType(string strID, string strUserType)
        {
            string strSql = string.Format("update sys_user set unitsadmin='{1}' where id='{0}'", strID, strUserType);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取单位信息
        /// </summary>
        /// <param name="strID">单位ID</param>
        ///<param name="strUnitsName">单位名称</param>
        ///<param name="strOrganization">组织代码</param>
        ///<param name="strNature">单位性质</param>
        ///<param name="strLegal">法人代表</param>
        /// <returns></returns>
        public DataTable GetUnits(string strID, string strUnitsName, string strOrganization, string strNature, string strLegal)
        {
            DataTable dtTemp = new DataTable();
            string strSql = string.Format(@"select id,
                                               unitsname,
                                               unitstel,
                                               unitsaddress,
                                               fax,
                                               postcode,
                                               legal,
                                               organization,
                                               nature
                                          from sys_units where 1=1 ");
            if (!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id='{0}'", strID);
            if (!string.IsNullOrEmpty(strUnitsName))
                strSql += string.Format(" and unitsname like '%{0}%'", strUnitsName);
            if (!string.IsNullOrEmpty(strOrganization))
                strSql += string.Format(" and organization like '%{0}%'", strOrganization);
            if (!string.IsNullOrEmpty(strNature))
                strSql += string.Format(" and nature like '%{0}%'", strNature);
            if (!string.IsNullOrEmpty(strLegal))
                strSql += string.Format(" and legal like '%{0}%'", strLegal);
            strSql += string.Format(" order by unitsname");
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 添加单位信息
        /// </summary>
        /// <param name="strunitsname">单位名称</param>
        /// <param name="strunitstel">单位电话</param>
        /// <param name="strunitsaddress">单位地址</param>
        /// <param name="strfax">传真</param>
        /// <param name="strpostcode">邮编</param>
        /// <param name="strlegal">法人代表</param>
        /// <param name="strorganization">组织代码</param>
        /// <param name="strnature">单位性质</param>
        public static void AddUnitsInfo(string strunitsname, string strunitstel, string strunitsaddress, string strfax, string strpostcode,
            string strlegal, string strorganization, string strnature)
        {
            try
            {
                string strSql = string.Format(@"insert into sys_units(unitsname,unitstel,unitsaddress,fax,postcode,legal,organization,nature)
 values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
                                strunitsname, strunitstel, strunitsaddress, strfax, strpostcode, strlegal, strorganization, strnature);
                SysParams.OAConnection().RunSql(strSql);
            }
            catch
            {
                throw new DBConcurrencyException("创建新单位失败！");
            }
        }

        /// <summary>
        /// 添加单位信息内容
        /// </summary>
        /// <param name="strUnitsName">单位名称</param>
        /// <param name="strUnitsTel">单位电话</param>
        /// <param name="strUnitsAddress">单位地址</param>
        /// <param name="strFax">传真</param>
        /// <param name="strPostCode">邮编</param>        
        /// <param name="strLegal">法人代表或负责人 </param>
        /// <param name="strOrganization">组织代码</param>
        /// <param name="strNature">单位性质</param>
        /// <returns></returns>
        public static string InsertUnits(string strUnitsName, string strUnitsTel, string strUnitsAddress, string strFax, string strPostCode,
            string strLegal, string strOrganization, string strNature)
        {
            Database dbGloab = BasicOperate.CreateNetOfficeDataBase();
            string strNewId = string.Empty;
            int nReturn = 0;
            string strSql = string.Format(@"insert into sys_units (UNITSNAME,UNITSTEL,UNITSADDRESS,FAX,POSTCODE,Organization,Legal,Nature) values(:unitsname,:unitstel,:unitsaddress,:fax,:postcode,:organization,:legal,:nature)  returning id into :newId");

            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

            dbGloab.AddInParameter(cmd, "unitsname", DbType.String, strUnitsName);
            dbGloab.AddInParameter(cmd, "unitstel", DbType.String, strUnitsTel);
            dbGloab.AddInParameter(cmd, "unitsaddress", DbType.String, strUnitsAddress);
            dbGloab.AddInParameter(cmd, "fax", DbType.String, strFax);
            dbGloab.AddInParameter(cmd, "postcode", DbType.String, strPostCode);
            dbGloab.AddInParameter(cmd, "organization", DbType.String, strOrganization);
            dbGloab.AddInParameter(cmd, "legal", DbType.String, strLegal);
            dbGloab.AddInParameter(cmd, "nature", DbType.String, strNature);
            dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);
            nReturn = dbGloab.ExecuteNonQuery(cmd);
            strNewId = dbGloab.GetParameterValue(cmd, "newId").ToString();
            return strNewId;
        }

        /// <summary>
        /// 修改单位信息
        /// </summary>
        /// <param name="strID">单位id</param>
        /// <param name="strunitsname">单位名称</param>
        /// <param name="strunitstel">单位电话</param>
        /// <param name="strunitsaddress">单位地址</param>
        /// <param name="strfax">传真</param>
        /// <param name="strpostcode">邮编</param>
        /// <param name="strlegal">法人代表</param>
        /// <param name="strorganization">组织代码</param>
        /// <param name="strnature">单位性质</param>
        public static void UpdateUnitsInfo(string strID, string strunitsname, string strunitstel, string strunitsaddress, string strfax, string strpostcode,
            string strlegal, string strorganization, string strnature)
        {
            try
            {
                string strSql = string.Format(@"update sys_units set unitsname='{0}',unitstel='{1}',unitsaddress='{2}',fax='{3}',postcode='{4}',
                                                legal='{5}',organization='{6}',nature='{7}' where id='{8}'", strunitsname, strunitstel,
                                                strunitsaddress, strfax, strpostcode, strlegal, strorganization, strnature, strID);
                SysParams.OAConnection().RunSql(strSql);
            }
            catch
            {
                throw new DBConcurrencyException("修改单位信息失败！");
            }
        }

       
        
    }
}
